﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model.Personnel_files;
using Model.ModiFication;
using Model.SystemManage;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DAL.Personneldal
{
    public class dal
    {
        private static string strCon = ConfigurationManager.ConnectionStrings["Constr"].ToString();
        public List<EmployeeLoginInfo_Tab> YGshow()//员工登录表显示
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from EmployeeLoginInfo_Tab inner join Position_Tab on EmployeeLoginInfo_Tab.PositionID = Position_Tab.PositionID", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<EmployeeLoginInfo_Tab> list = new List<EmployeeLoginInfo_Tab>();
            foreach (DataRow item in dt.Rows)
            {
                EmployeeLoginInfo_Tab model = new EmployeeLoginInfo_Tab();
                model.EmployID = Convert.ToInt32(item["EmployID"]);
                model.EmployName = item["EmployName"].ToString();
                model.DeptID = Convert.ToInt32(item["DeptID"]);
                model.PositionID = Convert.ToInt32(item["PositionID"]);
                model.PositionName = item["PositionName"].ToString();
                list.Add(model);
            }
            conn.Close();
            return list;
        }
        public List<Position_Tab> ZWshow()//员工职位表
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Position_Tab", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<Position_Tab> list = new List<Position_Tab>();
            foreach (DataRow item in dt.Rows)
            {
                Position_Tab model = new Position_Tab();
                model.PositionID = Convert.ToInt32(item["PositionID"]);
                model.PositionName = item["PositionName"].ToString();;
                list.Add(model);
            }
            conn.Close();
            return list;
        }           
        public int DAadd(DA da)//档案注册
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("insert into DA values(" + da.EmployID + ",'" + da.dabh + "','" + da.dname + "','" + da.beizhu + "')", conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public List<DA> DAshow()//档案表显示
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from DA inner join EmployeeLoginInfo_Tab on DA.EmployID = EmployeeLoginInfo_Tab.EmployID", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<DA> list = new List<DA>();
            foreach (DataRow item in dt.Rows)
            {
                DA model = new DA();
                model.EmployID = Convert.ToInt32(item["EmployID"]);
                model.Did = Convert.ToInt32(item["Did"]);
                model.dname = item["dname"].ToString();
                model.dabh = item["dabh"].ToString();
                model.beizhu = item["beizhu"].ToString();
                model.EmployName = item["EmployName"].ToString();
                list.Add(model);
            }
            conn.Close();
            return list;
        }
        public int DAdel(DA da)//档案删除
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("delete from DA where Did=" + da.Did + "", conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public DA DAEXist(DA da,int did)//档案编辑查询
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter dc = new SqlDataAdapter("select * from DA inner join EmployeeLoginInfo_Tab on DA.EmployID = EmployeeLoginInfo_Tab.EmployID where Did="+did+"", conn);
            DataTable dt = new DataTable();
            dc.Fill(dt);
            DA model = new DA();
            foreach (DataRow item in dt.Rows)
            {
                model.EmployID = Convert.ToInt32(item["EmployID"]);
                model.Did = Convert.ToInt32(item["Did"]);
                model.dname = item["dname"].ToString();
                model.dabh = item["dabh"].ToString();
                model.beizhu = item["beizhu"].ToString();
                model.EmployName = item["EmployName"].ToString();
            }
            return model;
        }
        public int DAupdate(int did,DA da)//档案修改
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("update DA set EmployID=" + da.EmployID + ",dabh='" + da.dabh + "',dname='" + da.dname + "',beizhu='"+da.beizhu+"' where Did="+did+"",conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public int HTadd(hetong ht)//合同添加
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("insert into hetong values(" + ht.EmployID+ ",'" + ht.hbh + "','" + ht.bdate + "','" + ht.edate + "'," + ht.PositionID + ",'" + ht.htnr + "')", conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public List<hetong> HTshow()//合同表显示
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from hetong inner join Position_Tab on hetong.positionid = Position_Tab.positionid inner join EmployeeLoginInfo_Tab on hetong.employid = EmployeeLoginInfo_Tab.employid", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<hetong> list = new List<hetong>();
            foreach (DataRow item in dt.Rows)
            {
                hetong model = new hetong();
                model.PositionID = Convert.ToInt32(item["PositionID"]);
                model.EmployID = Convert.ToInt32(item["EmployID"]);
                model.hid = Convert.ToInt32(item["hid"]);
                model.htnr = item["htnr"].ToString();
                model.hbh = item["hbh"].ToString();
                model.bdate = Convert.ToDateTime(item["bdate"]);
                model.edate = Convert.ToDateTime(item["edate"]);
                model.PositionName = item["PositionName"].ToString();
                model.EmployName = item["EmployName"].ToString();
                list.Add(model);
            }
            conn.Close();
            return list;
        }
        public int HTdel(int hid)//合同删除
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("delete from hetong where hid=" + hid + "", conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public hetong HTEXist(int hid)//合同表反填
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select* from hetong inner join EmployeeLoginInfo_Tab on hetong.EmployID = EmployeeLoginInfo_Tab.EmployID inner join Position_Tab on hetong.PositionID = Position_Tab.PositionID where hid = "+hid+"", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            hetong model = new hetong();
            foreach (DataRow item in dt.Rows)
            {
                model.PositionID = Convert.ToInt32(item["PositionID"]);
                model.PositionName = item["PositionName"].ToString();
                model.EmployName = item["EmployName"].ToString();
                model.hid = Convert.ToInt32(item["hid"]);
                model.hbh = item["hbh"].ToString();
                model.htnr = item["htnr"].ToString();
                model.bdate = Convert.ToDateTime(item["bdate"]);
                model.edate = Convert.ToDateTime(item["edate"]);
                model.EmployID =Convert.ToInt32(item["EmployID"]);
            }
            return model;
        }
        public int HTupdate(int hid, hetong he)//合同修改
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("update hetong set hbh='" + he.hbh + "',bdate='" + he.bdate + "',edate='" + he.edate + "',EmployID=" + he.EmployID + ",htnr='" + he.htnr + "',PositionID=" + he.PositionID + " where hid=" + hid + "", conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public List<hetong> HTCX(hetong he)//合同查询
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from hetong zname like='%" + he.EmployName + "%'", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<hetong> list = new List<hetong>();
            foreach (DataRow item in dt.Rows)
            {
                hetong model = new hetong();
                model.hid = Convert.ToInt32(item["hid"]);
                model.hbh = item["dabh"].ToString();
                model.bdate = Convert.ToDateTime(item["bdate"]);
                model.edate = Convert.ToDateTime(item["edate"]);
                model.EmployName = item["EmployName"].ToString();
            }
            return list;
        }                    
        public List<Model.Personnel_files.Dept_Tab> depshow()//部门表
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from Dept_Tab", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<Model.Personnel_files.Dept_Tab> list = new List<Model.Personnel_files.Dept_Tab>();
            foreach (DataRow item in dt.Rows)
            {
                Model.Personnel_files.Dept_Tab model = new Model.Personnel_files.Dept_Tab();
                model.DeptID = Convert.ToInt32(item["DeptID"]);
                model.DeptName = item["DeptName"].ToString();
                list.Add(model);
            }
            conn.Close();
            return list;
        }          
        public int reward_tabadd(reward_tab re)//奖罚表添加
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("insert into reward_tab values("+re.EmployID+",'"+re.retitle+"','"+re.redate+"',"+re.pid+")",conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public List<reward_tab> reward_tabshow()//奖罚表显示
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from reward_tab inner join price_tab on price_tab.pid = reward_tab.pid inner join EmployeeLoginInfo_Tab on reward_tab.EmployID = EmployeeLoginInfo_Tab.EmployID",conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<reward_tab> list = new List<reward_tab>();
            foreach (DataRow item in dt.Rows)
            {
                reward_tab model = new reward_tab();
                model.rid = Convert.ToInt32(item["rid"]);
                model.EmployID = Convert.ToInt32(item["EmployID"]);
                model.EmployName = item["EmployName"].ToString();
                model.pname = item["pname"].ToString();
                model.retitle = item["retitle"].ToString();
                model.redate = Convert.ToDateTime(item["redate"]);
                model.pid = Convert.ToInt32(item["pid"]);
                list.Add(model);
            }
            return list;
        }
        public List<price_tab> price_tabshow()//奖品表显示
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from price_tab", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            List<price_tab> list = new List<price_tab>();
            foreach (DataRow item in dt.Rows)
            {
                price_tab model = new price_tab();

                model.pname = item["pname"].ToString();
                model.pid = Convert.ToInt32(item["pid"]);
                list.Add(model);
            }
            return list;
        }
        public reward_tab reward_tabExist(int rid)//奖罚表反填
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter("select * from reward_tab inner join price_tab on price_tab.pid = reward_tab.pid where rid="+rid+"", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            reward_tab model = new reward_tab();
            foreach (DataRow item in dt.Rows)
            {              
                model.rid = Convert.ToInt32(item["rid"]);
                model.EmployID = Convert.ToInt32(item["EmployID"]);
                model.retitle = item["retitle"].ToString();
                model.redate = Convert.ToDateTime(item["redate"]);
                model.pid = Convert.ToInt32(item["pid"]);
            }
            return model;
        }
        public int reward_tabdel(int rid)//奖罚表删除
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("delete from reward_tab where rid="+rid+"", conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        public int reward_tabupdate(int rid, reward_tab re)//奖罚表修改
        {
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand("update reward_tab set EmployID=" + re.EmployID + ",retitle='" + re.retitle + "',redate='" + re.redate + "',pid="+re.pid+" where rid="+rid+"", conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }

    }
}
